<?php

namespace App\Exports;

use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;

use App\Services\Designer\Model\DesignerDemand;

class MydesignerListExport implements FromCollection, WithEvents , WithStrictNullComparison
{
    use Exportable;

    protected $params;
    protected $count;

    public function __construct(array $params)
    {
        $this->params = $params;
    }

    public function collection()
    {
        // 这里使用 collect 方法生成 collection，通常模型取出的数据即为 collection 格式
        $collection = new Collection($this->createData());
        return $collection;
    }

    protected function createData()
    {
        $heading = [
            ['订单数据数据列表','','','','','','','','',''],
            ['数据导出日期：'.date('Y年m月d日'),'','','','','','','','',''],
            ['标题','单号','设计师','金额','审核完成时间','状态','评价'],
        ];
        $data = $this->queryData($this->params);
        $this->count = count($data);
        return $data;
        return array_merge($heading, $data);
    }

    protected function queryData($request)
    {
        $designer_id = $request['designer_id'] ?? '';
        $title = $request['title'] ?? '';
        $designer_name = $request['designer_name'] ?? '';
        $status = $request['status'] ?? '';
        $onum = $request['onum'] ?? '';
        $verify_at_end = $request['verify_at_end'] ?? '';
        $verify_at_start = $request['verify_at_start'] ?? '';
        $size = $request['size'] ?? 10;
        
        $datas = DB::table('kd_designer_demands AS kddd')
		->leftJoin('users AS user', 'kddd.designer_id', '=', 'user.uid')
        ->leftJoin('k_crm_project AS crmp', 'crmp.id', '=', 'kddd.k_crm_project_id')
        ->leftJoin('k_designer_club_evaluation AS kdce', 'kdce.crm_oid', '=', 'kddd.crm_oid')
        ->leftJoin('kd_designer_demand_bidding AS ddb', function ($join) {
            $join->on('kddd.designer_id', '=', 'ddb.designer_id')
                ->On('kddd.id', '=', 'ddb.designer_demand_id');
        })
		->select([
            'kddd.id AS id',
			'kddd.title AS title',
			'kddd.onum AS onum',
            'kddd.crm_oid AS crm_oid',
            'user.name AS username',
            'crmp.df_grade AS df_grade',
            'kddd.last_finish_at AS last_finish_at',
            'kddd.status AS status',
            'kddd.verify_at AS verify_at',
            'ddb.bidding_charge AS bidding_charge',
            'kdce.crm_oid AS kdce_crm_oid'
			])
        ->when($title, function ($query) use ($title) {
            return $query->where('kddd.title', 'like', "%{$title}%");
        })
        ->when($designer_name, function ($query) use ($designer_name) {
            return $query->where('user.name', '=', "$designer_name");
        })
        ->when($status, function ($query) use ($status) {
            return $query->where('kddd.status', '=', "$status");
        })
        ->when($onum, function ($query) use ($onum) {
            return $query->where('kddd.onum', '=', "$onum");
        })
        ->when($verify_at_end, function ($query) use ($verify_at_end) {
            return $query->where('kddd.verify_at', '<=', "$verify_at_end");
        })
        ->when($verify_at_start, function ($query) use ($verify_at_start) {
            return $query->where('kddd.verify_at', '>=', "$verify_at_start");
        })
        ->when($designer_id, function ($query) use ($designer_id) {
            $alluser = array('10692','5473','4390');
            $isall = in_array($designer_id,$alluser);
            if($isall){
                return ;
            }else{
                return $query->where('kddd.designer_id', '=' ,$designer_id);
            }
            
        })
		// ->where('kddd.designer_id', '=' ,$designer_id)
        ->orderBy('kddd.id', 'DESC')
        ->groupBy('kddd.id')
		->get()->toArray();


        // $designer_id = $request['designer_id'] ?? '';
        // $datas = DB::table('kd_designer_demands AS kddd')
		// ->leftJoin('users AS user', 'kddd.designer_id', '=', 'user.uid')
        // ->leftJoin('k_crm_project AS crmp', 'crmp.id', '=', 'kddd.k_crm_project_id')
        // ->leftJoin('kd_designer_demand_bidding AS ddb', function ($join) {
        //     $join->on('kddd.designer_id', '=', 'ddb.designer_id')
        //         ->On('kddd.id', '=', 'ddb.designer_demand_id');
        // })
		// ->select([
        //     'kddd.id AS id',
		// 	'kddd.title AS title',
		// 	'kddd.onum AS onum',
        //     'user.name AS username',
        //     'crmp.df_grade AS df_grade',
        //     'kddd.last_finish_at AS last_finish_at',
        //     'kddd.status AS status',
        //     'kddd.verify_at AS verify_at',
        //     'ddb.bidding_charge AS bidding_charge'
		// 	])
		// ->where('kddd.designer_id', '=' ,$designer_id)
        // ->orderBy("kddd.verify_at", "desc")
		// ->get()->toArray();


        $res = [];
        foreach ($datas as $k=>$datum)
        {
            $res[$k][]= $datum->title;
            $res[$k][]= $datum->onum;
            $res[$k][]= $datum->username;
            $res[$k][]= $datum->bidding_charge;
            $res[$k][]= $datum->verify_at;
            switch($datum->status){
                case DesignerDemand::BIDDING: $res[$k][] = '寻找中';
                break;
                case DesignerDemand::DESIGNERING: $res[$k][] = '设计中';
                break;
                case DesignerDemand::COMPLETE: $res[$k][] = '已完工';
                break;
            }
            if(empty($datum->kdce_crm_oid)){
                $res[$k][]= '未评价';
            }else{
                $res[$k][]= '已评价';
            }

        }

        return $res;
    }

    /**
     * 注册事件
     * @return array
     */
    public function registerEvents(): array
    {
        return [
            AfterSheet::class  => function(AfterSheet $event) {
                //设置列宽
                $event->sheet->getDelegate()->getColumnDimension('A')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('B')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('C')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('D')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('E')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('F')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('G')->setWidth(20);
                //设置区域单元格字体、颜色、背景等，其他设置请查看 applyFromArray 方法，提供了注释
                $event->sheet->getDelegate()->getStyle('A1:G1')->applyFromArray([
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                        'wrapText' => true,
                    ],
                    'font' => [
                        'name' => '等线',
                        'size' => '22',
                        'bold' => true,
                        'italic' => false,
                        'strikethrough' => false,
                    ],
                ]);
                $event->sheet->getDelegate()->getStyle('A2:G2')->applyFromArray([
                    'font' => [
                        'name' => 'Arial',
                        'bold' => true,
                        'italic' => false,
                        'strikethrough' => false,
                        'color' => [
                            'rgb' => 'FF0000'
                        ]
                    ]
                ]);
                $event->sheet->getDelegate()->getStyle('A3:G3')->applyFromArray([
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                        'wrapText' => true,
                    ],
                    'font' => [
                        'name' => 'Arial',
                        'bold' => true,
                        'italic' => false,
                        'strikethrough' => false,
                        'color' => [
                            'rgb' => '4472C4'
                        ]
                    ],
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN,
                            'color' => [
                                'rgb' => '808080'
                            ]
                        ]
                    ],
                    'fill' => [
                        'fillType' => 'linear', //线性填充，类似渐变
                        'rotation' => 45, //渐变角度
                        'startColor' => [
                            'rgb' => 'F2F2F2' //初始颜色
                        ],
                        //结束颜色，如果需要单一背景色，请和初始颜色保持一致
                        'endColor' => [
                            'argb' => 'F2F2F2'
                        ]
                    ],
                ]);
                $event->sheet->getDelegate()->getStyle('A4:G'.(3+$this->count))->applyFromArray([
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                        'wrapText' => true,
                    ],
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN,
                            'color' => [
                                'rgb' => '808080'
                            ]
                        ]
                    ],
                ]);

                //合并单元格
                $event->sheet->getDelegate()->mergeCells('A1:G1');
                $event->sheet->getDelegate()->mergeCells('A2:G2');
            }
        ];
    }
}
